package com.yazdankhah.utils;

import com.yazdankhah.dal.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * This class prints table data.
 *
 * Author:	Ahmad Yazdankhah
 *
 */
public class TableUtil {

  /** Hold default field size. */
  private static final int DEFAULT_FIELD_SIZE = 20;

  /** Hold default null value. */
  private static final String DEFAULT_NULL_VALUE = " ";


  /**
   * find table columns size @precondition the table exists and the user has select privilege on it.
   *
   * @param rsmd the table meta data pointer
   *
   * @return columns size array
   */
  private static int[] getTableColSize(ResultSetMetaData rsmd) {
    try {
      int noOfCols = rsmd.getColumnCount();

      int[] colSizeArr = new int[noOfCols];

      for (int i = 0; i < noOfCols; i++) {
        colSizeArr[i] = rsmd.getColumnDisplaySize(i + 1);
      }

      return colSizeArr;

    } catch (SQLException e) {
      System.err.println(e.getMessage());
      return null;
    }

  } //method


  /**
   * find the optimum size of the columns as the following alg.: CSize > 20 | 20 hSize <= CSize len
   * = | | 20 hSize > CSize
   *
   * CSize <= 20 | cSize hSize <= CSize len = | | hSize hSize > CSize
   *
   * @param headerArr  the table columns array
   * @param colSizeArr the table columns size array
   *
   * @return the array of int containing the optimum size of the columns for displaying as a report.
   */
  private static int[] findColsOptSize(String[] colNameArr, int[] colSizeArr) {
    int count = colNameArr.length;
    int[] colOptSizeArr = new int[count];

    for (int i = 0; i < count; i++) {
      if (colSizeArr[i] > DEFAULT_FIELD_SIZE) {
        colOptSizeArr[i] = DEFAULT_FIELD_SIZE;
      } else {
        if (colSizeArr[i] <= DEFAULT_FIELD_SIZE
            && colNameArr[i].length() <= colSizeArr[i]) {
          colOptSizeArr[i] = colSizeArr[i];
        } else {
          colOptSizeArr[i] = colNameArr[i].length();
        }
      }

    } //for-loop

    return colOptSizeArr;

  } //method


  /**
   * get optimum size of each column and the columns themselves, then truncate or pad the values,
   * combine them and return it in one string.
   *
   * @param optSizeArr - the columns optimum size array
   * @param fieldsArr  - the table columns that should be optimum in size.
   *
   * @return the string of all columns combined. displaying as a report.
   */
  private static String rowMaker(int[] colOptSizeArr, String[] fieldsArr) {
    int count = colOptSizeArr.length;
    String rowStr = "";

    for (int i = 0; i < count; i++) {
      if (fieldsArr[i].length() > colOptSizeArr[i]) {
        rowStr += " " + fieldsArr[i].substring(0, colOptSizeArr[i]);
      } else {
        if (fieldsArr[i].length() == colOptSizeArr[i]) {
          rowStr += " " + fieldsArr[i];
        } else {
          int padSize = colOptSizeArr[i];
          String modFieldStr = String.format("%1$-" + padSize + "s",
                  fieldsArr[i]);
          rowStr += " " + modFieldStr;
        }
      }
    } // for-loop

    return rowStr.substring(1);

  } //method


  /**
   * make the report title under lines
   *
   * @param optSizeArr - the columns optimum size array
   *
   * @return the underline string
   */
  private static String titleUnderlineMaker(int[] colOptSizeArr) {
    int count = colOptSizeArr.length;

    String ch = "-";
    String rowStr = "";

    for (int i = 0; i < count; i++) {
      String fieldUnderline = "";

      for (int j = 0; j < colOptSizeArr[i]; j++) {
        fieldUnderline += ch;
      }

      rowStr += " " + fieldUnderline;
    } // for-loop

    return rowStr.substring(1);

  } //method


  /**
   * get one row of the table and make a string array.
   *
   * @param rs       the table result set
   * @param colCount - number of columns
   */
  private static String[] getOneRow(ResultSet rs, int colCount)
          throws SQLException, ClassNotFoundException {

    String[] colValue = new String[colCount];

    for (int i = 0; i < colCount; i++) {
      colValue[i] = rs.getString(i + 1);
      //Test: System.out.println(colValue[i]);

      if (colValue[i] == null) {
        colValue[i] = DEFAULT_NULL_VALUE;
      }

    } //for-loop

    return colValue;

  } //method


  /**
   * find table columns names @precondition the table exists and the user has select privilege on
   * it.
   *
   * @param rsmd the table meta data pointer
   *
   * @return columns names array
   */
  private static String[] getTableColName(ResultSetMetaData rsmd) {
    try {
      int noOfCols = rsmd.getColumnCount();
      String[] colNameArray = new String[noOfCols];

      for (int i = 0; i < noOfCols; i++) {
        colNameArray[i] = rsmd.getColumnName(i + 1);
      }

      return colNameArray;

    } //try
    catch (SQLException e) {
      System.err.println(e.getMessage());
      return null;
    }

  } //method

//...................... P U B L I C   M E T H O D S ........................//

  /**
   * print a ResultSet to system console.
   *
   * @param rs - the result set
   */
  public static void printResultSet(ResultSet rs) {
    try {
      ResultSetMetaData rsmd = rs.getMetaData();

      int colCount = rsmd.getColumnCount();

      String[] headerArr = getTableColName(rsmd);
      int[] colSizeArr = getTableColSize(rsmd);
      int[] colOptSizeArr = findColsOptSize(headerArr, colSizeArr);

      String rowStr = rowMaker(colOptSizeArr, headerArr);
      System.out.println(rowStr);
      String underlineStr = titleUnderlineMaker(colOptSizeArr);
      System.out.println(underlineStr);

      while (rs.next()) {
        String[] fieldsArr = getOneRow(rs, colCount);
        rowStr = rowMaker(colOptSizeArr, fieldsArr);
        System.out.println(rowStr);

      } //while-loop

      System.out.println("\n\n");
    } catch (SQLException | ClassNotFoundException ex) {
      System.err.println("SQL exception due to: " + ex.getMessage());
    }

  } //method


  private static void testprintResultSet()
          throws ClassNotFoundException, SQLException,
                 InstantiationException, IllegalAccessException {
//        ResultSet rs = queryRecipeByID(101);
//        printResultSet(rs);
  } //method.


  public static Connection getSingleConn() {

        String driverStr = "com.mysql.jdbc.Driver";
        String urlStr = "jdbc:mysql://localhost:3306/qt";
        String uid = "root";
        String pwd = "sesame";

        try {
            Class.forName(driverStr);
            return DriverManager.getConnection(urlStr, uid, pwd);

        } catch (SQLException | ClassNotFoundException ex) {
            System.err.println(ex.getMessage());
            return null;
        }
  } //method


  public static Connection getDatasourceConn00() {

    try {
      Properties props = new Properties();
      props.setProperty(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.cosnaming.CNCtxFactory");
      props.setProperty(Context.PROVIDER_URL, "jdbc:mysql://localhost:3306/qt");
      
      Context ctx = new InitialContext(props);
      
      DataSource ds = (DataSource) ctx.lookup("jdbc/qtDatasource2");
      return ds.getConnection();
      
    } catch (NamingException | SQLException ex) {
      System.err.println("No connection due to: " + ex.getMessage());
      return null;
    }

  } //method


  public static Connection getDatasourceConn() {

    try {
      Properties props = new Properties();
//      props.setProperty(Context.INITIAL_CONTEXT_FACTORY, "com.sun.enterprise.naming.SerialInitContextFactory");
//      props.setProperty(Context.PROVIDER_URL, "localhost:3306");
      
      Context ctx = new InitialContext(props);
      
      DataSource ds = (DataSource) ctx.lookup("jdbc/qtDatasource2");
      return ds.getConnection();
      
    } catch (NamingException | SQLException ex) {
      System.err.println("No connection due to: " + ex.getMessage());
      return null;
    }

  } //method


  /**
   * print a table to system console. @precondition the table exists and the user print on the top
   * of the table data.
   *
   * @param con
   * @param tabName
   */
  public static void printTable(Connection con, String tabName) {
    try {
      String queryStr = "SELECT * FROM " + tabName.toUpperCase();
      try (Statement stmnt = con.createStatement();
              ResultSet rs = stmnt.executeQuery(queryStr)) {

        printResultSet(rs);
      }

    } catch (SQLException ex) {
      System.err.println("SQL exception due to: " + ex.getMessage());
    }

  } // method


  private static void testprintTable() {
    Connection con = getDatasourceConn();
    String tabName = "bookmark";
    
    printTable(con, tabName);

  } //method


  /**
   */
//........................ M A I N   M E T H O D ............................//
  /**
   * This main method is just for testing this class.
   *
   * @param args the arguments
   */
  public static void main(String[] args) {

    testprintTable();

  } //main method

}
